package cn.edu.cdut.service;


import cn.edu.cdut.dao.StudentInfoDao;
import cn.edu.cdut.model.StudentInfo;
import cn.edu.cdut.utils.ReadExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

/**
 * Author  kobe
 * Created on 2017-05-25-11:48
 */
@Service
public class StuInfoService {

        @Autowired
        private StudentInfoDao studentInfoDao;

        //读取学生信息
        @Transactional
        public void readStuInfoExcel(String filename) throws IOException {

                //删除之前信息
                studentInfoDao.deleteAll();

                //读取信息
                File file= ResourceUtils.getFile("src/main/resources/static/" + filename);
                Workbook wb;//Excel工作簿
                Sheet sheet;
                String suff1 = ReadExcelUtil.parseFileSuffix(file);
                if (suff1.equals("xls")) {
                        wb = new HSSFWorkbook(new FileInputStream(file));
                        sheet = wb.getSheetAt(0);
                        readStuInfo2DB(sheet);
                } else if (suff1.equals("xlsx")) {
                        wb= new XSSFWorkbook(new FileInputStream(file));
                        sheet = wb.getSheetAt(0);
                        readStuInfo2DB(sheet);
                }
                Iterable<StudentInfo> stuInfoIterable = studentInfoDao.findByStuSubject("计算机信息管理");
                if (stuInfoIterable != null) {
                        studentInfoDao.delete(stuInfoIterable);
                }
        }

        private void readStuInfo2DB(Sheet sheet ) {
                //表头那一行
                Row fieldRow = sheet.getRow(0);
                short[] arrFieldIx = new short[5];
                short minColIx = fieldRow.getFirstCellNum();
                short maxColIx = fieldRow.getLastCellNum();
                for (short colIx = minColIx; colIx < maxColIx; colIx++) {
                        Cell fieldCell = fieldRow.getCell(colIx);
                        if (fieldCell == null) {
                                continue;
                        }
                        String fieldVal = fieldCell.getStringCellValue();
                        //学号0，姓名1，性别2，学院3，专业大类4
                        //可加入身份证号字段作为密码。
                        if (fieldVal.contains("学号")) {
                                arrFieldIx[0] = colIx;
                        } else if (fieldVal.contains("姓名")) {
                                arrFieldIx[1] = colIx;
                        } else if (fieldVal.contains("性别")) {
                                arrFieldIx[2] = colIx;
                        } else if (fieldVal.contains("学院")) {
                                arrFieldIx[3] = colIx;
                        } else if (fieldVal.contains("专业")) {
                                arrFieldIx[4] = colIx;
                        }
                }
                for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                        Row row = sheet.getRow(i);
                        String grade = row.getCell(arrFieldIx[0]).getStringCellValue().substring(0,4);
                        String stuID = row.getCell(arrFieldIx[0]).getStringCellValue();
                        String stuName = row.getCell(arrFieldIx[1]).getStringCellValue();
                        String sex = row.getCell(arrFieldIx[2]).getStringCellValue();
                        double score = 0;
                        String stuCollege = row.getCell(arrFieldIx[3]).getStringCellValue();
                        String stuSubject = row.getCell(arrFieldIx[4]).getStringCellValue();
                        String role = "student";
                        String password = "123456";
                        studentInfoDao.save(new StudentInfo(grade,stuID, stuName, sex, score, stuCollege, stuSubject, role, password));
                }
        }
}






